package br.com.fiap.DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import br.com.fiap.ConnectionFactory.ConnectionFactory;
import br.com.fiap.bean.Conta;
import br.com.fiap.bean.Transacao;

public class TransacaoDAO 
{
	public void incluirTransacao(Transacao transacao) throws ClassNotFoundException, SQLException
	{
		Connection conn = ConnectionFactory.getConnection();
		
		if(!transacao.getCodbarras().isEmpty())
		{
			String sql = "INSERT INTO TRANSACAO VALUES (0,?,?,?,?,?,?,?,null,null,null,null)";
			
			PreparedStatement stmt = conn.prepareStatement(sql);
			
			stmt.setDate(1, (Date) transacao.getData());
			stmt.setFloat(2, transacao.getValor());
			stmt.setString(3, transacao.getCodbarras());
			stmt.setInt(4, transacao.getContade().getNumero());
			stmt.setInt(5, transacao.getContade().getAgencia().getNumero());
			stmt.setInt(6, transacao.getContade().getTipo());
			stmt.setInt(7, transacao.getContade().getAgencia().getBanco().getNumero());
			
			stmt.executeUpdate();
		
			stmt.close();
		}	
		else
		{
			String sql = "INSERT INTO TRANSACAO VALUES (0,?,?,null,?,?,?,?,?,?,?,?)";
			
			PreparedStatement stmt = conn.prepareStatement(sql);
			
			stmt.setDate(1, (java.sql.Date) transacao.getData());
			stmt.setFloat(2, transacao.getValor());
			stmt.setInt(3, transacao.getContade().getNumero());
			stmt.setInt(4, transacao.getContade().getAgencia().getNumero());
			stmt.setInt(5, transacao.getContade().getTipo());
			stmt.setInt(6, transacao.getContade().getAgencia().getBanco().getNumero());
			stmt.setInt(7, transacao.getContaate().getNumero());
			stmt.setInt(8, transacao.getContaate().getAgencia().getNumero());
			stmt.setInt(9, transacao.getContaate().getTipo());
			stmt.setInt(10, transacao.getContaate().getAgencia().getBanco().getNumero());
			
			stmt.executeUpdate();
			
			stmt.close();
		}
		conn.close();
	}
	public ArrayList<Transacao> listarTransacoes(Conta conta, Date datade, Date dataate, int numult) throws SQLException, ClassNotFoundException
	{
		ArrayList<Transacao> transacoes = new ArrayList<Transacao>();
		
		Connection conn = ConnectionFactory.getConnection();
		
		String sql = "	SELECT id, data, valor, codbarras, contade, contadetipo, contadeagencia, contadebanco, contapara, contaparaagencia, contaparatipo, contaparabanco FROM TRANSACAO ";
		sql += " WHERE contade = ? and contadetipo = ? and contadebanco = ? and contadeagencia = ? ";
		if(numult <= 0)		
			sql += " and data between ? and ? ";
		sql += " and data <= CURDATE() ";
		sql += " UNION ALL ";
		sql += " SELECT id, data, valor, codbarras, contade, contadetipo, contadeagencia, contadebanco, contapara, contaparaagencia, contaparatipo, contaparabanco FROM TRANSACAO ";
		sql += " WHERE contapara = ? and contaparatipo = ? and contaparabanco = ? and contaparaagencia = ? ";
		if(numult <= 0)
			sql += " and data between ? and ?  ";
		sql += " and data <= CURDATE() ";
		if(numult <= 0)
			sql += " order by data";
		else
			sql += " order by data desc";
		
		PreparedStatement stmt = conn.prepareStatement(sql);
		int cont = 1;
		stmt.setInt(cont, conta.getNumero());
		stmt.setInt(++cont, conta.getTipo());
		stmt.setInt(++cont, conta.getAgencia().getBanco().getNumero());
		stmt.setInt(++cont, conta.getAgencia().getNumero());
		if(numult <= 0)
		{
			stmt.setDate(++cont, new java.sql.Date(datade.getTime()));
			stmt.setDate(++cont, new java.sql.Date(dataate.getTime()));	
		}
		stmt.setInt(++cont, conta.getNumero());
		stmt.setInt(++cont, conta.getTipo());
		stmt.setInt(++cont, conta.getAgencia().getBanco().getNumero());
		stmt.setInt(++cont, conta.getAgencia().getNumero());
		if(numult <= 0)
		{
			stmt.setDate(++cont, new java.sql.Date(datade.getTime()));
			stmt.setDate(++cont, new java.sql.Date(dataate.getTime()));	
		}
		
		ResultSet rs = stmt.executeQuery();
		//System.out.println(stmt.toString());
		cont = 1;
		while(rs.next())
		{
			if(numult > 0)
				if(cont > numult)
					break;
			
			Conta contapara = new Conta(rs.getInt("contaparaagencia"),rs.getInt("contapara"),rs.getInt("contaparatipo"),rs.getInt("contaparabanco"));
			Conta contade = new Conta(rs.getInt("contadeagencia"),rs.getInt("contade"),rs.getInt("contadetipo"),rs.getInt("contadebanco"));
			transacoes.add(new Transacao(rs.getInt("id"),rs.getString("codbarras"),contade,contapara,rs.getFloat("valor"),rs.getDate("data")));
			
			cont++;
		}

		conn.close();
		
		return transacoes;
	}
	
	public Transacao consultarTransacao(int id) throws ClassNotFoundException, SQLException
	{
		Connection conn = ConnectionFactory.getConnection();
		
		String sql = "	SELECT id, data, valor, codbarras, contade, contadetipo, contadeagencia, contadebanco, contapara, contaparaagencia, contaparatipo, contaparabanco FROM TRANSACAO ";
		sql += " WHERE id = ? ";
		
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, id);
		
		ResultSet rs = stmt.executeQuery();
		
		Transacao transacao = null;
		
		while(rs.next())
		{
			Conta contapara = new Conta(rs.getInt("contaparaagencia"),rs.getInt("contapara"),rs.getInt("contaparatipo"),rs.getInt("contaparabanco"));
			Conta contade = new Conta(rs.getInt("contadeagencia"),rs.getInt("contade"),rs.getInt("contadetipo"),rs.getInt("contadebanco"));
			transacao = new Transacao(rs.getInt("id"),rs.getString("codbarras"),contade,contapara,rs.getFloat("valor"),rs.getDate("data"));
		}

		conn.close();
		
		return transacao;		
	}
}
